/*
 * @author lb
 * 添加modify_tj(),modifyExamSchedule_tj
 */
package cn.edu.zjusc.ExcelGenerator;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.imageio.ImageIO;



import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.*;
import org.apache.poi.util.IOUtils;

import cn.edu.zjusc.ExcelModel.ExamSchedule;
import cn.edu.zjusc.ExcelModel.StudentInfo;

public class POIAdmissionTicketGenerator {
	/**
	 * The spreadsheet to read in
	 */
	private File inputWorkbook;
	/**
	 * The spreadsheet to output
	 */
	private File outputWorkbook;
	
	InputStream inp;
	FileOutputStream fileOut;
	private SimpleDateFormat mDateFormat = new SimpleDateFormat("yyyy-MM-dd");
	
	Workbook wb;
	

	

	//单证-自考
	public void modify(String input, String output, StudentInfo student, ExamSchedule schedule) throws InvalidFormatException, IOException, ParseException {
		inp = new FileInputStream(input);
		 
		 wb = WorkbookFactory.create(inp);
		 
		modifyPersonnelInfo(input,output,student);
		
		modifyExamSchedule_zj(input,output,schedule);
		
		fileOut = new FileOutputStream(output);
	    wb.write(fileOut);
	    fileOut.close();
		
	}
	//推荐免试 ,而这区别在于考试日程表不一样
	public void modify_tj(String input, String output, StudentInfo student, ExamSchedule schedule) throws InvalidFormatException, IOException, ParseException {
		inp = new FileInputStream(input);
		 
		 wb = WorkbookFactory.create(inp);
		 
		modifyPersonnelInfo(input,output,student);
		
		modifyExamSchedule_tj(input,output,schedule);
		
		fileOut = new FileOutputStream(output);
	    wb.write(fileOut);
	    fileOut.close();
		
	}
	//自主考试 -日程表修改
	private void modifyExamSchedule_zj(String input, String output, ExamSchedule schedule) throws InvalidFormatException, IOException, ParseException {

			System.out.println("-------modifyExamSchedule---------------");
		 	Sheet sheet = wb.getSheetAt(0);
		 	//时间格式
		 	Date dateA = mDateFormat.parse(schedule.getDateA());
			Date dateB = mDateFormat.parse(schedule.getDateB());
			Date base = mDateFormat.parse("1900-1-1");
			long milliPerDay = 1000 * 60 * 60 *24;
		 	
			//修改日期1
		 	Cell cell1=sheet.getRow(13).getCell(3);
		 	cell1.setCellValue((dateA.getTime()-base.getTime()) / milliPerDay+2);
		 	//修改日期2
		 	Cell cell2=sheet.getRow(13).getCell(5);
		 	cell2.setCellValue((dateB.getTime()-base.getTime()) / milliPerDay+2);
		 	//修改上午时间1
		 	Cell cell3=sheet.getRow(14).getCell(2);
			 cell3.setCellValue(schedule.getTimeStampAM());
			//修改下午时间2
			Cell cell4=sheet.getRow(16).getCell(2);
			cell4.setCellValue(schedule.getTimeStampPM());
			//修改科目1
			Cell cell5=sheet.getRow(14).getCell(3);
			cell5.setCellValue(schedule.getSubjectA());
			//修改科目2
			Cell cell6=sheet.getRow(16).getCell(3);
			cell6.setCellValue(schedule.getSubjectB());
			
	}
	//推荐免试-日程表修改
	private void modifyExamSchedule_tj(String input, String output, ExamSchedule schedule) throws InvalidFormatException, IOException, ParseException {

		System.out.println("-------modifyExamSchedule---------------");
	 	Sheet sheet = wb.getSheetAt(0);
	 	//时间格式
	 	Date dateA = mDateFormat.parse(schedule.getDateA());
		Date dateB = mDateFormat.parse(schedule.getDateB());
		Date base = mDateFormat.parse("1900-1-1");
		long milliPerDay = 1000 * 60 * 60 *24;
	 	
		//修改日期1
	 	Cell cell1=sheet.getRow(13).getCell(3);
	 	cell1.setCellValue((dateA.getTime()-base.getTime()) / milliPerDay+2);
	 	//修改日期2
	 	Cell cell2=sheet.getRow(13).getCell(5);
	 	//cell2.setCellValue((dateB.getTime()-base.getTime()) / milliPerDay+2);
	 	
	 	cell2.setCellValue("--");
	 	//修改上午时间1
	 	Cell cell3=sheet.getRow(14).getCell(2);
		 cell3.setCellValue(schedule.getTimeStampAM());
		//修改下午时间2
		Cell cell4=sheet.getRow(16).getCell(2);
		cell4.setCellValue(schedule.getTimeStampPM());
		//修改科目1
		Cell cell5=sheet.getRow(14).getCell(3);
		cell5.setCellValue(schedule.getSubjectA());
		//修改科目2
		Cell cell6=sheet.getRow(16).getCell(3);
		cell6.setCellValue("综合测试（面试）");
		
		//画出空白
		Cell cell7=sheet.getRow(14).getCell(5);
		cell7.setCellValue("--");
		Cell cell8=sheet.getRow(15).getCell(5);
		cell8.setCellValue("--");
		
		Cell cell10=sheet.getRow(16).getCell(5);
		cell10.setCellValue("--");
		Cell cell11=sheet.getRow(17).getCell(5);
		cell11.setCellValue("--");
		
}
	private void modifyPersonnelInfo(String input, String output, StudentInfo student) throws InvalidFormatException, IOException {
//		 inp = new FileInputStream(input);
//		 wb = WorkbookFactory.create(inp);
		System.out.println("--------modifyPersonnelInfo-------------");
		//获取sheet
		 Sheet sheet = wb.getSheetAt(0);
		//修改报名号
		 Cell cell1=sheet.getRow(1).getCell(2);
		 cell1.setCellValue("报名编号：("+student.getBaomingNo()+")");
		//修改时间抬头
		 Date date=new Date();
		 int year=date.getYear()+1900;
		 Cell cell2=sheet.getRow(2).getCell(2);
		 cell2.setCellValue(year+"年在职攻读硕士学位考试");
		//修改准考证
		 Cell cell3=sheet.getRow(6).getCell(3);
		 cell3.setCellValue(student.getAdmissionTicketID());
		//修改姓名
		 Cell cell4=sheet.getRow(7).getCell(3);
		 cell4.setCellValue(student.getName());
		//修改身份证号
		 Cell cell5=sheet.getRow(8).getCell(3);
		 cell5.setCellValue(student.getIdentityCardNo());
		//报考院校不需要修改
		 //工作单位
		 Cell cell6=sheet.getRow(10).getCell(3);
		 cell6.setCellValue(student.getWorkUnit());
		//修改考试地点
		 Cell cell7=sheet.getRow(19).getCell(3);
		 cell7.setCellValue(student.getExamPlace());
		//修改考场号
		 Cell cell8=sheet.getRow(20).getCell(3);
		 cell8.setCellValue(student.getKaochangHao());
		//修改座位号
		 Cell cell9=sheet.getRow(20).getCell(5);
		 cell9.setCellValue(student.getSeatNo());
		//修改照片
		//add picture data to this workbook.
		    InputStream is = new FileInputStream(student.getImagePath());
		    byte[] bytes = IOUtils.toByteArray(is);
		    int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
		    is.close();
		 CreationHelper helper = wb.getCreationHelper();
		// Create the drawing patriarch.  This is the top level container for all shapes. 
		Drawing drawing = sheet.createDrawingPatriarch();
		//add a picture shape
	    ClientAnchor anchor = helper.createClientAnchor();
	    //set top-left corner of the picture,
	    //subsequent call of Picture#resize() will operate relative to it
	    anchor.setCol1(5);
	    anchor.setRow1(6);
	    Picture pict = drawing.createPicture(anchor, pictureIdx);    
	    //auto-size picture relative to its top-left corner
	    pict.resize();
	}

	public static void main(String[] args) throws IOException, ParseException, InvalidFormatException {
		//自主考试
		ExamSchedule schedule = new ExamSchedule("2012-11-13", "2012-11-14", "08:30-11:30", "09:30-11:30",
				"英语1", "专业课");
		StudentInfo student = new StudentInfo("3301000002", "0833431Z600504",
				"seraph", "330106198402270036", "浙江大学",
				"家里蹲", "浙江大学软件学院", 12, 12, "./HeaderImage11.png");
		POIAdmissionTicketGenerator admissionTicketGenerator=new POIAdmissionTicketGenerator();
		admissionTicketGenerator.modify("./WebContent/self.xls", "./WebContent/self_zjNull.xls",student,schedule);
		
		//推荐
		
		
	}
}
